<?xml version="1.0" encoding="UTF-8" ?>
<%@ page language="java" contentType="text/html; charset=UTF-8" %>
<%@ page import="com.base.myproject.client.jdbc.DataSet,com.base.myproject.server.tools.*" %>
<%@ page import="java.util.Date,java.text.SimpleDateFormat,java.util.Calendar" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<LINK href="/hr_Jsp/report.css" rel=stylesheet>
<style type="text/css">
#40percentage {
	width: 100%;
	position: relative;
}
<!--
td {
	text-align: center;
}
-->
</style>
</head>
<body>
<%
	String start = request.getParameter("start");
	String end = request.getParameter("end");
	
	if(start == null && end == null) {
		Date defaultDate = new Date();
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		Calendar canlandar = Calendar.getInstance();
		canlandar.setTime(defaultDate);
		canlandar.add(canlandar.DATE, -3);
		start = format.format(canlandar.getTime());
		end = format.format(defaultDate);
	}
	DataSetTool DST = new DataSetTool();
	String sql1 = "select * from (select _sum as sum1,name,position,(select count(*) from hr_positionRequest where hr_positionRequest.recruitmentId = recruitment and informationForm = '网络' and requestDate between '" + start + "' and '" + end + "') as web,(select count(*) from hr_positionRequest where hr_positionRequest.recruitmentId = recruitment and informationForm = '海报' and requestDate between '" + start + "' and '" + end + "') as posters,(select count(*) from hr_positionRequest where hr_positionRequest.recruitmentId = recruitment and informationForm = '招聘会' and requestDate between '" + start + "' and '" + end + "') as recruitment,(select count(*) from hr_positionRequest where hr_positionRequest.recruitmentId = recruitment and informationForm = '员工介绍' and requestDate between '" + start + "' and '" + end + "') as employs,(select count(*) from hr_positionRequest where hr_positionRequest.recruitmentId = recruitment and informationForm = '其他' and requestDate between '" + start + "' and '" + end + "') as other,(select count(*) from hr_positionRequest where hr_positionRequest.recruitmentId = recruitment and reason='工资' and requestDate between '" + start + "' and '" + end + "') as reasonOfWages,(select count(*) from hr_positionRequest where hr_positionRequest.recruitmentId = recruitment and reason='班次安排' and requestDate between '" + start + "' and '" + end + "') as reasonOfDiv,(select count(*) from hr_positionRequest where hr_positionRequest.recruitmentId = recruitment and reason='不符合部门要求' and requestDate between '" + start + "' and '" + end + "') as reasonOfDep,(select count(*) from hr_positionRequest where hr_positionRequest.recruitmentId = recruitment and reason='其他' and requestDate between '" + start + "' and '" + end + "') as reasonOfOther,(select count(*) from hr_positionRequest where hr_positionRequest.recruitmentId = recruitment and situation='录用' and requestDate between '" + start + "' and '" + end + "') as situationOfEmployed,(select count(*) from hr_positionRequest where hr_positionRequest.recruitmentId = recruitment and situation='暂缓' and requestDate between '" + start + "' and '" + end + "') as situationOfDefer,(select count(*) from hr_positionRequest where hr_positionRequest.recruitmentId = recruitment and situation='待定' and requestDate between '" + start + "' and '" + end + "') as situationOfBacklog from (select count(*) as _sum,recruitmentId as recruitment,position, name from hr_positionRequest, hr_recruitmentRequest, systeminfo where hr_positionRequest.requestDate between '" + start + "' and '" + end + "' and hr_recruitmentRequest.depCode like '";
	String sql2 = "%' and hr_recruitmentRequest.icode = hr_positionRequest.recruitmentId and systeminfo.id = hr_recruitmentRequest.depCode group by recruitmentId,position, name) as inners) as a left join (select _sum,title,(select count(*) from bcode_job where employstatus = '离职' and Resignationdate between '" + start + "' and '" + end + "' and bcode_job.employinnertitle = title and personalleavereason = '工资') as leaveOfWages,(select count(*) from bcode_job where employstatus = '离职' and Resignationdate between '" + start + "' and '" + end + "' and bcode_job.employinnertitle = title and personalleavereason = '找到新工作') as leaveOfOffice,(select count(*) from bcode_job where employstatus = '离职' and Resignationdate between '" + start + "' and '" + end + "' and bcode_job.employinnertitle = title and personalleavereason = '不适应') as leaveOfAdapt,(select count(*) from bcode_job where employstatus = '离职' and Resignationdate between '" + start + "' and '" + end + "' and bcode_job.employinnertitle = title and personalleavereason = '其他') as leaveOfOther from (select count(*) as _sum, employinnertitle as title from bcode_job,bcode where  bcode_job.bcode = bcode.bcode and employstatus = '离职' and Resignationdate between '" + start + "' and '" + end + "' and bcode.systemid like '";
	String sql3 = "%' group by employinnertitle) as inners) as b on b.title = a.position";
	String systemSql = "select * from (select distinct substring(depCode,0,5) systemid from hr_positionRequest, hr_recruitmentRequest where hr_positionRequest.requestDate between '" + start + "' and '" + end + "' and hr_recruitmentRequest.icode = hr_positionRequest.recruitmentId union select distinct substring(systemid ,0,5) as systemid from bcode_job,bcode where  bcode_job.bcode = bcode.bcode and employstatus = '离职' and Resignationdate between '" + start + "' and '" + end + "') as a inner join (select name,id from systeminfo where len(id) = 4) as b on a.systemid = b.id";
	DataSet system =DST.getDataSet(systemSql);
	
	if (system.getRowCount() == 0) {
%>
你所查询的时间段暂时没有记录
<%
	} else {
%>
<%= start %> 到 <%= end %> 的人事报表如下：
<table width="100%" style="border-collapse: collapse" border="1"  cellspacing="0" cellpadding="3" bordercolordark="#CCCCCC" bordercolorlight="#CCCCCC" style="line-height: 120%;border-color:#000000;font-size:10px">
  <tr>
    <td rowspan="4">&#37096;&#38376;</td>
    <td rowspan="4">&#20998;&#37096;</td>
    <td rowspan="4">&#24212;&#32856;&#32844;&#20301;</td>
    <td colspan="15">&#25307;&#32856;&#24773;&#20917;</td>
    <td colspan="6">&#31163;&#32844;&#24773;&#20917;</td>
  </tr>
  <tr>
    <td rowspan="3">&#27714;&#32844;&#20154;&#25968;</td>
    <td colspan="5">&#20449;&#24687;&#26469;&#28304;</td>
    <td colspan="6">&#38754;&#35797;&#24773;&#20917;</td>
    <td rowspan="3">&#24405;&#29992;<br />
    &#20154;&#25968;</td>
    <td rowspan="3">&#23454;&#38469;<br />
      &#20837;&#32844;<br />
      &#20154;&#25968;</td>
    <td rowspan="3">&#22791;&#27880;</td>
    <td rowspan="3">&#31163;&#32844;&#20154;&#25968;</td>
    <td colspan="4">&#31163;&#32844;&#21407;&#22240;</td>
    <td rowspan="3">&#22791;&#27880;</td>
  </tr>
  <tr>
    <td rowspan="2">&#32593;&#32476;</td>
    <td rowspan="2">&#28023;&#25253;</td>
    <td rowspan="2">&#25307;&#32856;<br />
    &#20250;</td>
    <td rowspan="2">&#21592;&#24037;<br />
    &#20171;&#32461;</td>
    <td rowspan="2">&#20854;&#20182;</td>
    <td colspan="4">&#19981;&#36866;&#21512;&#21407;&#22240;</td>
    <td rowspan="2">&#24453;<br />
      &#23450;<br />
      &#20013;</td>
    <td rowspan="2">&#26242;<br />
    &#32531;</td>
    <td rowspan="2">&#24037;&#36164;</td>
    <td rowspan="2">&#25214;&#21040;&#26032;<br />
    &#24037;&#20316;</td>
    <td rowspan="2">&#19981;&#36866;<br />
      &#24212;</td>
    <td rowspan="2">&#20854;&#20182; </td>
  </tr>
  <tr>
    <td>&#24037;&#36164;</td>
    <td>&#29677;&#27425;<br />
    &#23433;&#25490;</td>
    <td>&#19981;&#31526;&#21512;&#37096;<br />
      &#38376;&#35201;&#27714;</td>
    <td>&#20854;&#20182;</td>
  </tr>
  <%
  	for (int i=0; i<system.getRowCount(); i++) {
  		String id = system.getValue(i, "id");
  		String sql = sql1 + id + sql2 + id + sql3;
  		String name = system.getValue(i, "name");
  		DataSet statments =DST.getDataSet(sql);
  		for (int j=0; j<statments.getRowCount(); j++) {
  			String sum = statments.getValue(j, "_sum") == null? "0" : statments.getValue(j, "_sum");
  			String leaveOfWages = statments.getValue(j, "leaveOfWages") == null? "0" : statments.getValue(j, "leaveOfWages");
  			String leaveOfOffice = statments.getValue(j, "leaveOfOffice") == null? "0" : statments.getValue(j, "leaveOfOffice");
  			String leaveOfAdapt = statments.getValue(j, "leaveOfAdapt") == null? "0" : statments.getValue(j, "leaveOfAdapt");
  			String leaveOfOther = statments.getValue(j, "leaveOfOther") == null? "0" : statments.getValue(j, "leaveOfOther");
  %>
  <tr>
  <% if (j == 0) { %>
    <td rowspan="<%= statments.getRowCount() %>"><%= name %></td>
  <% } %>
    <td><%= statments.getValue(j, "name") %></td>
    <td><%= statments.getValue(j, "position") %></td>
    <td><%= statments.getValue(j, "sum1") %></td>
    <td><%= statments.getValue(j, "web") %></td>
    <td><%= statments.getValue(j, "posters") %></td>
    <td><%= statments.getValue(j, "recruitment") %></td>
    <td><%= statments.getValue(j, "employs") %></td>
    <td><%= statments.getValue(j, "other") %></td>
    <td><%= statments.getValue(j, "reasonOfWages") %></td>
    <td><%= statments.getValue(j, "reasonOfDiv") %></td>
    <td><%= statments.getValue(j, "reasonOfDep") %></td>
    <td><%= statments.getValue(j, "reasonOfOther") %></td>
    <td><%= statments.getValue(j, "situationOfEmployed") %></td>
    <td><%= statments.getValue(j, "situationOfDefer") %></td>
    <td><%= statments.getValue(j, "situationOfBacklog") %></td>
    <td></td>
    <td></td>
    <td><%= sum %></td>
    <td><%= leaveOfWages %></td>
    <td><%= leaveOfOffice %></td>
    <td><%= leaveOfAdapt %></td>
    <td><%= leaveOfOther %></td>
    <td></td>
  </tr>
<%
  		}
	}
%>
</table>
<%
	}
%>
</body>
</html>